﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using IronOne.SalesOne.BusinessObjects;
using System.Linq.Dynamic;
using System.Configuration;

namespace IronOne.SalesOne.DataObjects
{
   public class LinqNeedAnalysis
    {
       public int pageSize = Convert.ToInt32(ConfigurationManager.AppSettings["GridCount"]);

       public List<NeedsAnalysis> GetNeedAnalysisCustomers(NeedAnalysisSearch nas, int pageNo)
       {

           StringBuilder uis = new StringBuilder();

           //unit
           if (nas.UnitName != null)
           {
               int j = 0;
               foreach (SalesOneUnit un in nas.UnitName)
               {

                   if (j == 0)
                   {
                       if (un.UnitId != 0)
                           uis.Append(" AND (NodeId=" + un.UnitId.ToString() + " AND UserTypeId = 4)");

                   }
                   else
                   {
                       if (un.UnitId != 0)
                           uis.Append(" OR (NodeId=" + un.UnitId.ToString() + " AND UserTypeId = 4)");

                   }

                   j++;
               }

           }



           //branch
           if (nas.BranchName != null)
           {
               int k = 0;
               foreach (Branch br in nas.BranchName)
               {

                   if (k == 0)
                   {
                       if (br.BranchId != 0 && nas.UnitName == null)
                           uis.Append(" AND (NodeId=" + br.BranchId.ToString() + " AND UserTypeId = 3)");
                   }
                   else
                   {
                       if (br.BranchId != 0 && nas.UnitName == null)
                           uis.Append(" OR (NodeId=" + br.BranchId.ToString() + " AND UserTypeId = 3)");
                   }

                   k++;
               }
           }


           //so
           StringBuilder need = new StringBuilder();
           if (nas.So != null)
           {
               int i = 0;
               foreach (string so in nas.So)
               {
                   if (i == 0)
                   {
                       if (!String.IsNullOrEmpty(so) && so != "Select")
                           need.Append(" AND SOCode = \"" + so + "\"");
                   }
                   else
                   {
                       if (!String.IsNullOrEmpty(so) && so != "Select")
                           need.Append(" OR SOCode = \"" + so + "\"");
                   }

                   i++;
               }
           }

           if (nas.NeedAnalysisNo != 0)
               need.Append(" AND NeedsAnalysisNo = " + nas.NeedAnalysisNo );
           if (!String.IsNullOrEmpty(nas.ProspectId))
               need.Append(" AND CustomerID = \"" + nas.ProspectId + "\"");



           //StringBuilder cus = new StringBuilder();

           //if (!String.IsNullOrEmpty(nas.Name))
           //    cus.Append(" AND FirstName = \"" + nas.Name + "\"");
           //if (!String.IsNullOrEmpty(nas.IdNo) && !String.IsNullOrEmpty(nas.IdType))
           //    cus.Append(" AND IDNumber = \"" + nas.IdNo + "\"" + " AND IDType = " + nas.IdType);


           var cusPred = PredicateBuilder.True<CustomerEntity>();
           if (!String.IsNullOrEmpty(nas.LastName))
               cusPred = cusPred.And(s => s.FirstName.Contains(nas.LastName));
           if (!String.IsNullOrEmpty(nas.IdNo) && !String.IsNullOrEmpty(nas.IdType))
           {
               cusPred = cusPred.And(s => s.IDNumber == nas.IdNo);
               cusPred = cusPred.And(s => s.IDType == int.Parse(nas.IdType));
           }


           

           //StringBuilder prosp = new StringBuilder();

           //if (!String.IsNullOrEmpty(nas.ProspectId))
           //    prosp.Append(" AND ProspectID = \"" + nas.ProspectId + "\"");



           if (uis.Length > 1 || need.Length > 1 )
           {
               if (uis.ToString().StartsWith(" AND "))
                   uis.Remove(0, 5);
               if (need.ToString().StartsWith(" AND "))
                   need.Remove(0, 5);
               //if (cus.ToString().StartsWith(" AND "))
               //    cus.Remove(0, 5);
               //if (prosp.ToString().StartsWith(" AND "))
               //    prosp.Remove(0, 5);


               using (SODataDataContext db = DataContextFactory.CreateContext())
               {
                   IQueryable<NeedsAnalysisDetailEntity> query = db.NeedsAnalysisDetailEntities;


                   //if (prosp.Length >1)
                   //{
                   //    if (uis.Length > 1)
                   //    {
                   //        if (need.Length > 1 && cus.Length > 1)
                   //        {
                   //            query = from needA in db.NeedsAnalysisDetailEntities.Where(need.ToString())
                   //                    join cust in db.CustomerEntities.Where(cus.ToString())
                   //                    on needA.CustomerID equals cust.CustomerID
                   //                    join usr in db.UserEntities
                   //                    on cust.SOCode equals usr.SOCode
                   //                    join us in db.UsersInStructureEntities.Where(uis.ToString())
                   //                    on usr.UserId equals us.UserId
                   //                    join pr in db.ProspectEntities.Where(prosp.ToString())
                   //                    on cust.CustomerID equals pr.CustomerID
                   //                    select needA;

                   //        }
                   //        else if (need.Length > 1 && cus.Length < 1)
                   //        {
                   //            query = from needA in db.NeedsAnalysisDetailEntities.Where(need.ToString())
                   //                    join cust in db.CustomerEntities
                   //                    on needA.CustomerID equals cust.CustomerID
                   //                    join usr in db.UserEntities
                   //                    on cust.SOCode equals usr.SOCode
                   //                    join us in db.UsersInStructureEntities.Where(uis.ToString())
                   //                    on usr.UserId equals us.UserId
                   //                    join pr in db.ProspectEntities.Where(prosp.ToString())
                   //                    on cust.CustomerID equals pr.CustomerID
                   //                    select needA;

                   //        }
                   //        else if (need.Length < 1 && cus.Length > 1)
                   //        {
                   //            query = from needA in db.NeedsAnalysisDetailEntities
                   //                    join cust in db.CustomerEntities.Where(cus.ToString())
                   //                    on needA.CustomerID equals cust.CustomerID
                   //                    join usr in db.UserEntities
                   //                    on cust.SOCode equals usr.SOCode
                   //                    join us in db.UsersInStructureEntities.Where(uis.ToString())
                   //                    on usr.UserId equals us.UserId
                   //                    join pr in db.ProspectEntities.Where(prosp.ToString())
                   //                    on cust.CustomerID equals pr.CustomerID
                   //                    select needA;
                   //        }
                   //        else if (need.Length < 1 && cus.Length < 1)
                   //        {
                   //            query = from needA in db.NeedsAnalysisDetailEntities
                   //                    join cust in db.CustomerEntities
                   //                    on needA.CustomerID equals cust.CustomerID
                   //                    join usr in db.UserEntities
                   //                    on cust.SOCode equals usr.SOCode
                   //                    join us in db.UsersInStructureEntities.Where(uis.ToString())
                   //                    on usr.UserId equals us.UserId
                   //                    join pr in db.ProspectEntities.Where(prosp.ToString())
                   //                    on cust.CustomerID equals pr.CustomerID
                   //                    select needA;

                   //        }

                   //    }
                   //    else
                   //    {

                   //        if (need.Length > 1 && cus.Length > 1)
                   //        {
                   //            query = from needA in db.NeedsAnalysisDetailEntities.Where(need.ToString())
                   //                    join cust in db.CustomerEntities.Where(cus.ToString())
                   //                    on needA.CustomerID equals cust.CustomerID
                   //                    join usr in db.UserEntities
                   //                    on cust.SOCode equals usr.SOCode
                   //                    join pr in db.ProspectEntities.Where(prosp.ToString())
                   //                    on cust.CustomerID equals pr.CustomerID
                   //                    select needA;

                   //        }
                   //        else if (need.Length > 1 && cus.Length < 1)
                   //        {
                   //            query = from needA in db.NeedsAnalysisDetailEntities.Where(need.ToString())
                   //                    join cust in db.CustomerEntities
                   //                    on needA.CustomerID equals cust.CustomerID
                   //                    join usr in db.UserEntities
                   //                    on cust.SOCode equals usr.SOCode
                   //                    join pr in db.ProspectEntities.Where(prosp.ToString())
                   //                    on cust.CustomerID equals pr.CustomerID
                   //                    select needA;

                   //        }
                   //        else if (need.Length < 1 && cus.Length > 1)
                   //        {
                   //            query = from needA in db.NeedsAnalysisDetailEntities
                   //                    join cust in db.CustomerEntities.Where(cus.ToString())
                   //                    on needA.CustomerID equals cust.CustomerID
                   //                    join usr in db.UserEntities
                   //                    on cust.SOCode equals usr.SOCode
                   //                    join pr in db.ProspectEntities.Where(prosp.ToString())
                   //                    on cust.CustomerID equals pr.CustomerID
                   //                    select needA;
                   //        }
                   //        else if (need.Length < 1 && cus.Length < 1)
                   //        {
                   //            query = from needA in db.NeedsAnalysisDetailEntities
                   //                    join cust in db.CustomerEntities
                   //                    on needA.CustomerID equals cust.CustomerID
                   //                    join usr in db.UserEntities
                   //                    on cust.SOCode equals usr.SOCode
                   //                    join pr in db.ProspectEntities.Where(prosp.ToString())
                   //                    on cust.CustomerID equals pr.CustomerID
                   //                    select needA;

                   //        }

                   //    }
                   //}
                   //else
                   //{
                       if (uis.Length > 1)
                       {
                           if (need.Length > 1 && cusPred != null)
                           {
                               query = from needA in db.NeedsAnalysisDetailEntities.Where(need.ToString())
                                       join cust in db.CustomerEntities.Where(cusPred)
                                       on needA.CustomerID equals cust.CustomerID
                                       join usr in db.UserEntities
                                       on cust.SOCode equals usr.SOCode
                                       join us in db.UsersInStructureEntities.Where(uis.ToString())
                                       on usr.UserId equals us.UserId
                                       select needA;

                           }
                           else if (need.Length > 1 && cusPred == null)
                           {
                               query = from needA in db.NeedsAnalysisDetailEntities.Where(need.ToString())
                                       join cust in db.CustomerEntities
                                       on needA.CustomerID equals cust.CustomerID
                                       join usr in db.UserEntities
                                       on cust.SOCode equals usr.SOCode
                                       join us in db.UsersInStructureEntities.Where(uis.ToString())
                                       on usr.UserId equals us.UserId
                                       select needA;

                           }
                           else if (need.Length < 1 && cusPred != null)
                           {
                               query = from needA in db.NeedsAnalysisDetailEntities
                                       join cust in db.CustomerEntities.Where(cusPred)
                                       on needA.CustomerID equals cust.CustomerID
                                       join usr in db.UserEntities
                                       on cust.SOCode equals usr.SOCode
                                       join us in db.UsersInStructureEntities.Where(uis.ToString())
                                       on usr.UserId equals us.UserId
                                       select needA;
                           }
                           else if (need.Length < 1 && cusPred == null)
                           {
                               query = from needA in db.NeedsAnalysisDetailEntities
                                       join cust in db.CustomerEntities
                                       on needA.CustomerID equals cust.CustomerID
                                       join usr in db.UserEntities
                                       on cust.SOCode equals usr.SOCode
                                       join us in db.UsersInStructureEntities.Where(uis.ToString())
                                       on usr.UserId equals us.UserId
                                       select needA;

                           }

                       }
                       else
                       {

                           if (need.Length > 1 && cusPred != null)
                           {
                               query = from needA in db.NeedsAnalysisDetailEntities.Where(need.ToString())
                                       join cust in db.CustomerEntities.Where(cusPred)
                                       on needA.CustomerID equals cust.CustomerID
                                       join usr in db.UserEntities
                                       on cust.SOCode equals usr.SOCode
                                       select needA;

                           }
                           else if (need.Length > 1 && cusPred == null)
                           {
                               query = from needA in db.NeedsAnalysisDetailEntities.Where(need.ToString())
                                       select needA;

                           }
                           else if (need.Length < 1 && cusPred != null)
                           {
                               query = from needA in db.NeedsAnalysisDetailEntities
                                       join cust in db.CustomerEntities.Where(cusPred)
                                       on needA.CustomerID equals cust.CustomerID
                                       join usr in db.UserEntities
                                       on cust.SOCode equals usr.SOCode
                                       select needA;
                           }
                           

                       }
                   //}



                   IQueryable<NeedsAnalysisDetailEntity> pageData = query.Skip((pageNo - 1) * pageSize).Take(pageSize);

                   return pageData.Select(c => Mapper.ToNeedsAnalysisObject(c)).ToList();
               }
           }
           else
               return null;



       }

       public NeedsAnalysis GetNeedAnalysis(int needAnNo)
       {
           using (SODataDataContext db = DataContextFactory.CreateContext())
           {
               return Mapper.ToNeedsAnalysisObject(db.NeedsAnalysisDetailEntities
                           .SingleOrDefault(p => p.NeedsAnalysisNo == needAnNo));
           }
       }


       public int UpdateNeedAnalysis(NeedsAnalysis need)
       {
           using (SODataDataContext db = DataContextFactory.CreateContext())
           {

               try
               {
                   NeedsAnalysisDetailEntity entity = Mapper.ToNeedsAnalysisEntity(need);
                   db.NeedsAnalysisDetailEntities.Attach(entity, true);
                   db.SubmitChanges();
                   return 1;
               }
               catch (ChangeConflictException)
               {
                   throw new Exception("A change to contact record was made before your changes.");
               }
               catch (Exception)
               {
                   return 0;
               }
           }
       }

       //public List<NeedsAnalysis> GetNeedAnalysisCustomers(NeedsAnalysis needAnalysis)
       //{
       //    StringBuilder qry = new StringBuilder();


       //    using (SODataDataContext db = DataContextFactory.CreateContext())
       //    {
       //        //if (product.ProductCategoryCode != 0)
       //        //{
       //        //    qry.Append("ProductCategoryCode=" + product.ProductCategoryCode);
       //        // if (needAnalysis.IdNo != "" && needAnalysis.IdType != "")
       //        if (needAnalysis.IdNo != "")
       //        {
       //            qry = qry.Append(" IDNumber =\"" + needAnalysis.IdNo + "\"");
       //        }
       //        if (needAnalysis.IdNo != "" && needAnalysis.ProspectId != "" && needAnalysis.NeedAnalysisNo != 0)
       //        {
       //            qry = qry.Append(" AND " + " ProspectID =\"" + needAnalysis.ProspectId + "\"");

       //        }
             
       //        IQueryable<NeedsAnalysisDetailEntity> query = db.NeedsAnalysisDetailEntities;
       //        //IQueryable<NeedsAnalysisDetailEntity> query = db.NeedsAnalysisDetailEntities;


       //        query = db.NeedsAnalysisDetailEntities.Where(qry.ToString());
       //        //var pageData = query.Skip((page - 1) * pageSize).Take(pageSize);
       //        return query.Select(c => Mapper.ToNeedsAnalysisObject(c)).ToList();

       //    }
       //}

    }
}
